﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.LightSwitch;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace LightSwitchApplication.Server
{
    public class DataAccess
    {

        public static void UpdateAddress(string AddressId, string Latitude, string Longitude)
        {
            string connString = GetConnectionString();
            if (String.IsNullOrEmpty(connString)) return;
            if (String.IsNullOrEmpty(AddressId)) return;
            AddressId = StopInjection(AddressId);
            //Location = StopInjection(Location);
            Latitude = StopInjection(Latitude);
            Longitude = StopInjection(Longitude);
            #region Request DB
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.Text;
                    cmdGetData.CommandText = "UPDATE dbo.Addresses SET LATITUDE = isnull(@Latitude,LATITUDE), LONGITUDE = isnull(@Longitude,LONGITUDE) "
                        + "WHERE Id = @AddressId";
                    cmdGetData.Parameters.Add("AddressId", SqlDbType.Int);
                    //cmdGetData.Parameters.Add("Location", SqlDbType.VarChar, 1000);
                    cmdGetData.Parameters.Add("Latitude", SqlDbType.Float);
                    cmdGetData.Parameters.Add("Longitude", SqlDbType.Float);
                    cmdGetData.Parameters["AddressId"].Value = int.Parse(AddressId);

                    //cmdGetData.Parameters["Location"].Value = Location;
                    cmdGetData.Parameters["Latitude"].Value = Latitude;
                    cmdGetData.Parameters["Longitude"].Value = Longitude;
                    cmdGetData.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    //Console.WriteLine(ex.Message);
                    //throw;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }

                //using (var dwspace = new DataWorkspace())
                //{
                //    Address address = dwspace.ApplicationData.Addresses.Where(add => add.Id.ToString() == AddressId).FirstOrDefault();
                //    if (address == null) return;
                //    address.Latitude = double.Parse(Latitude);
                //    address.Longitude = double.Parse(Longitude);
                //    dwspace.ApplicationData.SaveChanges();
                //}
            }
            #endregion
        }

        public static string GetAddress(string AddressId)
        {
            string result = string.Empty;
            string connString = GetConnectionString();
            if (String.IsNullOrEmpty(connString)) return result;
            if (String.IsNullOrEmpty(AddressId)) return result;
            AddressId = StopInjection(AddressId);
            #region Request DB
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.Text;
                    cmdGetData.CommandText = "SELECT REPLACE("+
                        "isnull(adr.[AddressLine1]+', ','') "+
                        "+isnull(adr.[AddressLine2]+', ','') "+
                        "+isnull(ct.[Name]+', ','') "+
                        "+isnull(stp.[Name]+', ','') "+
                        "+isnull(cntr.[Name]+', ','') "+
                        "+isnull(adr.[PostalCode]+', ','') +', ',', , ','') " +
                        "FROM [dbo].[Addresses] adr "+
                        "LEFT OUTER JOIN dbo.[Cities] ct ON adr.[City_Address] = ct.[Id] "+
                        "LEFT OUTER JOIN dbo.[StateProvinces] stp ON adr.[Address_StateProvince] = stp.[Id] "+
                        "LEFT OUTER JOIN dbo.[CountryRegions] cntr ON adr.[Address_CountryRegion] = cntr.[Id] "+
                        "WHERE adr.[Id]=@AddressId;";
                    cmdGetData.Parameters.Add("AddressId", SqlDbType.Int);
                    cmdGetData.Parameters["AddressId"].Value = int.Parse(AddressId);

                    SqlDataReader dr = cmdGetData.ExecuteReader();
                    while (dr.Read())
                    {
                        result = dr[0].ToString();
                        break;
                    }
                }
                catch (SqlException)
                {
                    //Console.WriteLine(ex.Message);
                    //throw;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }
            #endregion
            return result;
        }

        public static string GetSystemParameterValue(string ParameterName)
        {
            string result = string.Empty;
            string connString = GetConnectionString();
            if (String.IsNullOrEmpty(connString)) return result;
            if (String.IsNullOrEmpty(ParameterName)) return result;
            ParameterName = StopInjection(ParameterName);
            #region Request DB
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.Text;
                    cmdGetData.CommandText = "select top 1 Value from dbo.SystemParameters where Name = @Name";
                    cmdGetData.Parameters.Add("Name", SqlDbType.VarChar, 255);
                    cmdGetData.Parameters["Name"].Value = ParameterName;
                    result = (string)cmdGetData.ExecuteScalar();
                }
                catch (SqlException)
                {
                    //Console.WriteLine(ex.Message);
                    //throw;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }

            }
            #endregion
            return result;
        }

        //public static Visit GetVisitData(string VisitId)
        //{
        //    string connString = GetConnectionString();
        //    Visit result = new Visit();
        //    #region Request DB
        //    using (SqlConnection conn = new SqlConnection(connString))
        //    {
        //        try
        //        {
        //            conn.Open();
        //            SqlCommand cmdGetData = new SqlCommand();
        //            cmdGetData.Connection = conn;
        //            cmdGetData.CommandType = CommandType.Text;
        //            cmdGetData.CommandText = "SELECT TOP 1 [Id],[VisitTimeFrom],[VisitTimeTo],[VisitDescription],[VisitGoalAchieved],[Visit_Shop],[SalesPerson_Visit] FROM [SalesDep].[dbo].[Visits]"
        //                + "WHERE Id = @VisitId";
        //            cmdGetData.Parameters.Add("VisitId", SqlDbType.VarChar, 20);
        //            cmdGetData.Parameters["VisitId"].Value = VisitId;
        //            SqlDataReader dr = cmdGetData.ExecuteReader();
        //            while (true)
        //            {
        //                result.Id = (int)dr["Id"];
        //                result.VisitTimeFrom = (DateTime)dr["VisitTimeFrom"];
        //                result.VisitTimeTo = (DateTime?)dr["VisitTimeTo"];
        //                result.VisitDescription = (string)(dr["VisitDescription"] != DBNull.Value ? dr["VisitDescription"] : "");
        //            }
        //        }
        //        catch (SqlException)
        //        {
        //            //Console.WriteLine(ex.Message);
        //            //throw;
        //        }
        //        finally
        //        {
        //            if (conn.State != ConnectionState.Closed) conn.Close();
        //        }

        //    }
        //    #endregion
        //    return result;
        //}

        private static string StopInjection(string value)
        {
            value = value.ToLower().Replace("delete", "");
            value = value.ToLower().Replace("drop", "");
            value = value.ToLower().Replace("update", "");
            value = value.ToLower().Replace("insert", "");
            value = value.ToLower().Replace("create", "");
            return value;
        }

        //private static string GetConnectionString()
        //{
        //    string result = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["LightSwitchConnectionString"].ConnectionString;
        //}
        private static string GetConnectionString()
        {
            string result = ConfigurationManager.ConnectionStrings[1].ConnectionString; //"LocalSqlServer"
            return result;
        }
    }
}